import pymysql
import xlrd

parentPath = r'C:\Users\da\Desktop\\'  # 全局父路径
parentTitle = r'商品名称'  # 第一行商品名称需要去除

connect = pymysql.Connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='123456',
    db='shop',
    charset='utf8'
)


def pricelevel(p1, market, name):
    # 价格区间  p1套餐价   market市场价

    if market != "":
        sellprice1 = str(round(p1 - float(market), 2))
    else:
        sellprice1 = ""
        print(str(name) + "   市场价为空")
    return sellprice1


def setShopStandard():
    # 第一步
    # 设置商品规格

    rbook = xlrd.open_workbook(parentPath + '最终售价.xls')
    rbook.sheets()
    rsheet = rbook.sheet_by_index(0)
    cursor = connect.cursor()

    for row in rsheet.get_rows():
        if row[0].value != parentTitle:
            goods = "select goods_id from ecs_goods where goods_name='" + row[0].value + "'"
            cursor.execute(goods)
            goods = cursor.fetchall()

            # 查询是否存在商品
            if len(goods) != 0:
                goodsId = str(goods[0][0])
                name1 = str(row[14].value)
                name2 = str(row[16].value)
                name3 = str(row[18].value)
                if str(row[14].value) != "":
                    sellprice1 = pricelevel(row[15].value, row[9].value, row[0])

                if str(row[16].value) != "":
                    sellprice2 = pricelevel(row[17].value, row[9].value, row[0])

                if str(row[18].value) != "":
                    sellprice3 = pricelevel(row[19].value, row[9].value, row[0])

                if name1 != "":
                    sql = "INSERT INTO ecs_goods_attr(goods_id,attr_id,attr_value,attr_price,attr_sort_order) VALUES (" + goodsId + ",9,'" + name1 + "','" + sellprice1 + "','1')"
                    cursor.execute(sql)

                if name2 != "":
                    sql = "INSERT INTO ecs_goods_attr(goods_id,attr_id,attr_value,attr_price,attr_sort_order) VALUES (" + goodsId + ",9,'" + name2 + "','" + sellprice2 + "','1')"
                    cursor.execute(sql)

                if name3 != "":
                    sql = "INSERT INTO ecs_goods_attr(goods_id,attr_id,attr_value,attr_price,attr_sort_order) VALUES (" + goodsId + ",9,'" + name3 + "','" + sellprice3 + "','1')"
                    cursor.execute(sql)
            else:
                print("不存在：" + row[0].value)
    print("结束")


def setShopAttr():
    # 第二步
    # 商品规格建立完善关联

    rbook = xlrd.open_workbook(parentPath + '2.xls')
    rbook.sheets()
    rsheet = rbook.sheet_by_index(0)
    cursor = connect.cursor()
    snIndex = 1

    for row in rsheet.get_rows():
        goodsAttr = str(row[0].value)
        goodsId = str(row[1].value)
        productSn = 'sp000100g_p' + str(snIndex).rjust(5, '0')
        productNum = '500'
        isCheck = '1'

        sql = "INSERT INTO ecs_products(goods_id,goods_attr,product_sn,product_number,is_check) VALUES ('" + goodsId + "','" + goodsAttr + "','" + productSn + "','" + productNum + "','" + isCheck + "')"
        cursor.execute(sql)
        snIndex = snIndex + 1
        print(sql)


def shopGrade():
    # 促销活动，给商品分不同等级
    cursor = connect.cursor()
    goods = "select goods_id,shop_price from ecs_goods"
    cursor.execute(goods)
    goods = cursor.fetchall()

    # 查询是否存在商品
    if len(goods) != 0:
        for good in goods:
            if 0 < good[1] <= 20:
                sql = "INSERT INTO ecs_goods_cat(goods_id,cat_id) VALUES ('" + str(good[0]) + "','142')"
                cursor.execute(sql)
            elif 20 < good[1] <= 30:
                sql = "INSERT INTO ecs_goods_cat(goods_id,cat_id) VALUES ('" + str(good[0]) + "','141')"
                cursor.execute(sql)
            elif 30 < good[1] <= 50:
                sql = "INSERT INTO ecs_goods_cat(goods_id,cat_id) VALUES ('" + str(good[0]) + "','140')"
                cursor.execute(sql)


def setShopType():
    # 给商品设置类型
    cursor = connect.cursor()
    goods = "select goods_name,goods_id from ecs_goods where cat_id=118"
    cursor.execute(goods)
    goods = cursor.fetchall()
    # 查询是否存在商品
    if len(goods) != 0:
        for good in goods:
            if containAny(['巧克力'], good[0]):
                sql = "INSERT INTO ecs_goods_attr(goods_id,attr_id,attr_value) VALUES (" + str(
                    good[1]) + ",16,'" + '巧克力' + "')"
            elif containAny(['粗粮'], good[0]):
                sql = "INSERT INTO ecs_goods_attr(goods_id,attr_id,attr_value) VALUES (" + str(
                    good[1]) + ",16,'" + '粗粮' + "')"
            elif containAny(['锅巴'], good[0]):
                sql = "INSERT INTO ecs_goods_attr(goods_id,attr_id,attr_value) VALUES (" + str(
                    good[1]) + ",16,'" + '锅巴' + "')"
            elif containAny(['蛋卷'], good[0]):
                sql = "INSERT INTO ecs_goods_attr(goods_id,attr_id,attr_value) VALUES (" + str(
                    good[1]) + ",16,'" + '蛋卷' + "')"
            elif containAny(['果冻'], good[0]):
                sql = "INSERT INTO ecs_goods_attr(goods_id,attr_id,attr_value) VALUES (" + str(
                    good[1]) + ",16,'" + '果冻' + "')"
            elif containAny(['糖'], good[0]):
                sql = "INSERT INTO ecs_goods_attr(goods_id,attr_id,attr_value) VALUES (" + str(
                    good[1]) + ",16,'" + '糖果' + "')"
            elif containAny(['酥'], good[0]):
                sql = "INSERT INTO ecs_goods_attr(goods_id,attr_id,attr_value) VALUES (" + str(
                    good[1]) + ",16,'" + '酥饼' + "')"
            elif containAny(['薯片', '薯条', '膨化'], good[0]):
                sql = "INSERT INTO ecs_goods_attr(goods_id,attr_id,attr_value) VALUES (" + str(
                    good[1]) + ",16,'" + '薯片/薯条' + "')"
            elif containAny(['饼干'], good[0]):
                sql = "INSERT INTO ecs_goods_attr(goods_id,attr_id,attr_value) VALUES (" + str(
                    good[1]) + ",16,'" + '饼干' + "')"
            else:
                sql = "INSERT INTO ecs_goods_attr(goods_id,attr_id,attr_value) VALUES (" + str(
                    good[1]) + ",16,'" + '其他' + "')"
            cursor.execute(sql)
        print("结束")


def containAny(array, str):
    # 判断包含关系
    for c in array:
        if c in str:
            return True
    return False


if __name__ == '__main__':
    setShopAttr()
